--exec sp_load_releases_to_modify_spec_file 'sort'

/**
  * SELECT * FROM SORT_RELEASE_TABLE
  * SELECT SORT_REL_DATE FROM SORT_RELEASE_TABLE
  * SELECT PART_NUMBER FROM PRODUCT
  * SELECT Assembly_Release_Date FROM ASSM_RELEASE_TABLE
  */
ALTER  PROCEDURE sp_load_releases_to_modify_spec_file
(
@process_type VARCHAR(50)
)
AS
BEGIN

	DECLARE @release_id INT
	DECLARE @release_number VARCHAR(50)
	DECLARE @processstep_id INT
	DECLARE @part_number VARCHAR(50)
	DECLARE @spec_file_id INT
	DECLARE @spec_file_name VARCHAR(100)
	DECLARE	@revision VARCHAR(50)
	DECLARE @rel_date VARCHAR(50)

	DECLARE @current_spec_id_for_part_number INT

	DECLARE  @releases_cursor CURSOR
	DECLARE @results TABLE ( SELECTED BIT, SORT_REL_ID INT,SORT_RELEASE_NO VARCHAR(50), SpecFile_Id INT, Spec_File_Name VARCHAR(100), Revision VARCHAR(50), RDATE VARCHAR(50), PART_NUMBER VARCHAR(50) )

	IF ( @process_type = 'Sort' )
	BEGIN

		--Getting All the Sort Releases whose Status is Issued or Approved.
		SET @releases_cursor = CURSOR FOR SELECT SORT_REL_ID, SORT_REL_NUMB, PROCESSSTEP_ID, SPEC_FILE_ID,SORT_REL_DATE  FROM SORT_RELEASE_TABLE WHERE STATUS IN ( 'Issued', 'Approved' )
		OPEN @releases_cursor
		FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		WHILE @@FETCh_STATUS = 0
		BEGIN
			SET @current_spec_id_for_part_number = NULL
			--Getting the Current Spec File Id for The Part Number
			SELECT @current_spec_id_for_part_number = ST.SpecFile_Id FROM SPEC_TABLE ST, PRODUCT_SPEC_TABLE PST WHERE ST.SpecFile_Id = PST.SpecFile_Id AND ProcessStep_Id = @processstep_id
			
			IF ( @current_spec_id_for_part_number IS NOT NULL AND @current_spec_id_for_part_number <> @spec_file_id )
			BEGIN
				--Getting the Part Number
				SELECT @part_number = PART_NUMBER FROM PRODUCT WHERE ProcessStep_Id = @processstep_id

				--Getting the Spec File Name and Revision Numer
				SELECT @spec_file_name = Spec_File_Name, @revision =  REVISION FROM SPEC_TABLE WHERE SpecFile_Id = @current_spec_id_for_part_number
				INSERT INTO @results VALUES ( 0, @release_id, @release_number, @spec_file_id, @spec_file_name, @revision, @rel_date, @part_number )
			END

			SET @release_id = NULL
			SET @release_number = NULL
			SET @processstep_id = NULL
			SET @spec_file_id = NULL
			SET @rel_date = NULL

			FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		END

		CLOSE @releases_cursor
		DEALLOCATE @releases_cursor

	END
	ELSE IF ( @process_type = 'Assembly' )
	BEGIN
		--Getting All the Assembly Releases whose Status is Issued or Approved.
		--SET @releases_cursor = CURSOR FOR SELECT Assembly_Release_Id, Assembly_Release_No, PROCESSSTEP_ID, ASSEMBLY_SPEC_FILE_ID, Assembly_Release_Date FROM ASSM_RELEASE_TABLE WHERE STATUS IN ( 'Issued', 'Approved' )

		/*
		 This Cursor is modified to fetch all the records that are in Assembly Process. Even thou the Assembly release is closed
		*/

		SET @releases_cursor = CURSOR FOR ( ( SELECT Assembly_Release_Id, Assembly_Release_No, PROCESSSTEP_ID, ASSEMBLY_SPEC_FILE_ID, Assembly_Release_Date FROM ASSM_RELEASE_TABLE WHERE STATUS IN ( 'Issued', 'Approved' ) )
			UNION
			( SELECT Assembly_Release_Id, Assembly_Release_No, ASSM_RELEASE_TABLE.PROCESSSTEP_ID, ASSEMBLY_SPEC_FILE_ID, Assembly_Release_Date 
			FROM ASSM_RELEASE_TABLE, Lot_Table, Process_Step WHERE ASSM_RELEASE_TABLE.Assembly_Release_No = Lot_Table.Lot_Number 
			AND Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID And Lot_Current_Status = 'Running' AND PS_Type_Name = 'Assembly' ) )

		OPEN @releases_cursor
		FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		WHILE @@FETCh_STATUS = 0
		BEGIN
			SET @current_spec_id_for_part_number = NULL
			--Getting the Current Spec File Id for The Part Number
			SELECT @current_spec_id_for_part_number = ST.SpecFile_Id FROM SPEC_TABLE ST, PRODUCT_SPEC_TABLE PST WHERE ST.SpecFile_Id = PST.SpecFile_Id AND ProcessStep_Id = @processstep_id

			IF ( @current_spec_id_for_part_number IS NOT NULL AND @current_spec_id_for_part_number <> @spec_file_id )
			BEGIN
				--Getting the Part Number
				SELECT @part_number = PART_NUMBER FROM PRODUCT WHERE ProcessStep_Id = @processstep_id

				--Getting the Spec File Name and Revision Numer
				SELECT @spec_file_name = Spec_File_Name, @revision =  REVISION FROM SPEC_TABLE WHERE SpecFile_Id = @current_spec_id_for_part_number
				INSERT INTO @results VALUES ( 0, @release_id, @release_number, @spec_file_id, @spec_file_name, @revision, @rel_date, @part_number )
			END
			SET @release_id = NULL
			SET @release_number = NULL
			SET @processstep_id = NULL
			SET @spec_file_id = NULL
			SET @rel_date = NULL

			FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		END

		CLOSE @releases_cursor
		DEALLOCATE @releases_cursor
	END
	ELSE IF ( @process_type = 'Test' )
	BEGIN
		--Getting All the Sort Releases whose Status is Issued or Approved.
		-- SET @releases_cursor = CURSOR FOR SELECT Assembly_Release_Id, Assembly_Release_No,PROCESSSTEP_ID, TEST_SPEC_FILE_ID, Assembly_Release_Date FROM ASSM_RELEASE_TABLE WHERE STATUS IN ( 'Issued', 'Approved' )

		SET @releases_cursor = CURSOR FOR 
		SELECT Assembly_Release_Id, Assembly_Release_No,PROCESSSTEP_ID, TEST_SPEC_FILE_ID, Assembly_Release_Date 
		FROM ASSM_RELEASE_TABLE WHERE STATUS IN ( 'Issued', 'Approved' )
		UNION
		select Assembly_Release_Id, Assembly_Release_No, ASSM_RELEASE_TABLE.PROCESSSTEP_ID, TEST_SPEC_FILE_ID, Assembly_Release_Date 
		FROM ASSM_RELEASE_TABLE, Lot_Table, Process_Step Where ASSM_RELEASE_TABLE.Assembly_Release_No = Lot_Table.Lot_Number 
		AND Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID And Lot_Current_Status = 'Running' AND PS_Type_Name = 'Assembly'
		UNION 
		select Assembly_Release_Id, Assembly_Release_No, ASSM_RELEASE_TABLE.PROCESSSTEP_ID, TEST_SPEC_FILE_ID, Assembly_Release_Date 
		FROM ASSM_RELEASE_TABLE, Lot_Table, Process_Step Where ASSM_RELEASE_TABLE.Assembly_Release_No = Lot_Table.Lot_Number 
		AND Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID And Lot_Current_Status = 'Running' AND PS_Type_Name = 'Test'

		OPEN @releases_cursor
		FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		WHILE @@FETCh_STATUS = 0
		BEGIN
			DECLARE @PROCESS_STEP_ID_FOR_TEST INT
			SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @processstep_id
			SELECT @PROCESS_STEP_ID_FOR_TEST = P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'TEST'

			SET @current_spec_id_for_part_number = NULL
			--Getting the Current Spec File Id for The Part Number
			SELECT @current_spec_id_for_part_number = ST.SpecFile_Id FROM SPEC_TABLE ST, PRODUCT_SPEC_TABLE PST WHERE ST.SpecFile_Id = PST.SpecFile_Id AND ProcessStep_Id = @PROCESS_STEP_ID_FOR_TEST

			IF ( @current_spec_id_for_part_number IS NOT NULL AND @current_spec_id_for_part_number <> @spec_file_id )
			BEGIN
				--Getting the Part Number
				SELECT @part_number = PART_NUMBER FROM PRODUCT WHERE ProcessStep_Id = @processstep_id

				--Getting the Spec File Name and Revision Numer
				SELECT @spec_file_name = Spec_File_Name, @revision =  REVISION FROM SPEC_TABLE WHERE SpecFile_Id = @current_spec_id_for_part_number
				INSERT INTO @results VALUES ( 0, @release_id, @release_number, @spec_file_id, @spec_file_name, @revision, @rel_date, @part_number )
			END

			SET @release_id = NULL
			SET @release_number = NULL
			SET @processstep_id = NULL
			SET @spec_file_id = NULL
			SET @rel_date = NULL

			FETCH NEXT FROM @releases_cursor INTO @release_id, @release_number, @processstep_id, @spec_file_id, @rel_date
		END

		CLOSE @releases_cursor
		DEALLOCATE @releases_cursor

	END


	SELECT * FROM @results ORDER BY CONVERT( DATETIME, RDATE, 101) DESC
END